package com.yml.mybatis.gererator.service;

import com.yml.mybatis.gererator.meta.ColumnInfo;
import com.yml.mybatis.gererator.meta.DataSourceInfo;
import com.yml.mybatis.gererator.meta.TableInfo;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author YML
 * @since 2023-07-20 10:13
 */
public class SqlServerTable implements ITableService {

    private final String QUERY_TABLE_SQL = "SELECT a.object_id,a.name," +
            "CONVERT(NVARCHAR(100),isnull(g.value,'-')) as comment " +
            "from sys.tables a " +
            "left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)";

    private final DataSourceInfo db;

    public SqlServerTable(DataSourceInfo db) {
        this.db = db;
    }

    @Override
    public List<TableInfo> queryAllTables() throws SQLException {
        List<TableInfo> tableInfos = new ArrayList<>();
        try (
                Connection connection = this.db.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(QUERY_TABLE_SQL);
                ResultSet resultSet = preparedStatement.executeQuery();
        ) {
            while (resultSet.next()) {
                long id = resultSet.getLong("object_id");
                String name = resultSet.getString("name");
                TableInfo tableInfo = new TableInfo(id, name);
                tableInfo.setComment(resultSet.getString("comment"));
                tableInfos.add(tableInfo);
            }
        }
        return tableInfos;
    }

    @Override
    public List<TableInfo> queryTables(String... tables) throws SQLException {
        List<TableInfo> tableInfos = queryAllTables();
        List<String> list = List.of(tables);
        return tableInfos.stream().filter(v -> list.contains(v.getTableName())).collect(Collectors.toList());
    }

    @Override
    public List<ColumnInfo> queryColumns(Object table) throws SQLException {
        List<ColumnInfo> list = new ArrayList<>();
        long tableId = (long) table;
        String builder = "select " + "c.id as table_id,c.colid,c.name,t.name as type,p.value as comment," +
                "( CASE c.isnullable WHEN 0 THEN 'Y' ELSE 'N' END ) AS not_null," +
                "tm.text as default_value,t2.is_primary_key " +
                "from sys.syscolumns c " +
                "LEFT JOIN sys.systypes t on c.xtype = t.xusertype " +
                "left join sys.syscomments tm on c.cdefault = tm.id " +
                "left join sys.extended_properties p on c.id = p.major_id and c.colid = p.minor_id " +
                "left join (" +
                "  SELECT idxc.object_id,idxc.column_id,idx.is_primary_key FROM sys.indexes idx" +
                "  LEFT JOIN sys.index_columns idxc on idx.object_id = idxc.object_id where idx.object_id = " +
                tableId + " and idx.is_primary_key  = 1 " +
                " GROUP BY idxc.object_id,idxc.column_id,idx.is_primary_key" +
                ") t2 on  t2.column_id = c.colid " +
                "where c.id = " +
                tableId;
        try (Connection connection = this.db.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(builder);
             ResultSet resultSet = preparedStatement.executeQuery();
        ) {
            while (resultSet.next()) {
                long colid = resultSet.getLong("colid");
                String name = resultSet.getString("name");
                String type = resultSet.getString("type");
                String comment = resultSet.getString("comment");
                String notNull = resultSet.getString("not_null");
                String defaultValue = resultSet.getString("default_value");
                if (StringUtils.isNotBlank(defaultValue)) {
                    if (StringUtils.equals(defaultValue, "(NULL)")) {
                        defaultValue = "NULL";
                    } else if (defaultValue.startsWith("((")) {
                        defaultValue = defaultValue.substring(2, defaultValue.length() - 2);
                    }
                }
                ColumnInfo column = new ColumnInfo(tableId, colid, name, type);
                column.setComment(comment);
                column.setDefaultValue(defaultValue);
                column.setNotNull(notNull);
                column.setPrimaryKey(resultSet.getString("is_primary_key"));
                list.add(column);
            }
        }
        return list;
    }
}
